import datetime
import json

from flask import Blueprint, request, jsonify, Response, stream_with_context, render_template
from db_dto_helper import db_file_path
import os
import sqlite3
from datetime import datetime

setapi = Blueprint("dataset",__name__)
url_prefix = '/dataset'
"""
create table  base_dataset
/**EZDML_DESC_START**
base_dataset(数据表9)
------------------------------------------------
id(编号)                        PKInteger
orderNum(排序号)                Float
systemContent(系统内容)         String(255)
userContent(用户内容)           String(255)
assistantContent(助手内容)      String(255)
**EZDML_DESC_END**/
(
       id                INTEGER /*primary_key*/ not null  /*编号*/,
       orderNum          NUMERIC  /*排序号*/,
       systemContent     TEXT(255)  /*系统内容*/,
       userContent       TEXT(255)  /*用户内容*/,
       assistantContent  TEXT(255)  /*助手内容*/,
       constraint        PK_base_dataset_id primary key (id)
);

"""
@setapi.route('/index',methods=['GET'])
def data_set_index():
    return render_template('dataset.html')

@setapi.route('/list',methods=['GET'])
def data_set_list():
    conn = sqlite3.connect(db_file_path)
    conn.row_factory = sqlite3.Row
    sql = """  select * from  base_dataset order by  orderNum"""
    cursor = conn.cursor()
    cursor.execute(sql )
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data_list = [dict(row) for row in rows]
    return jsonify({"code":1,"data":data_list})


@setapi.route('/create',methods=['POST'])
def data_set_create():
    conn = sqlite3.connect(db_file_path)
    order_num = request.json.get('orderNum')
    system_content = request.json.get('systemContent')
    user_content = request.json.get('userContent')
    assistant_content = request.json.get('assistantContent')
    sql = """ 
        insert into base_dataset(id,orderNum, systemContent,userContent,assistantContent) 
        values( ?,?,?,?,?);
        """
    cursor = conn.cursor()
    row_id = datetime.now().timestamp()
    cursor.execute(sql, (int(row_id), order_num, system_content, user_content, assistant_content))
    cursor.close()
    conn.commit()
    conn.close()
    return jsonify({"code":1,"msg":"save ok!"})

@setapi.route('/modify',methods=['POST'])
def data_set_modify():
    conn = sqlite3.connect(db_file_path)
    row_id = request.json.get('id')
    order_num = request.json.get('orderNum')
    system_content = request.json.get('systemContent')
    user_content = request.json.get('userContent')
    assistant_content = request.json.get('assistantContent')
    sql = """ 
        update base_dataset set orderNum = ?, systemContent =?,userContent =?,assistantContent =?
        where id = ?;
        """
    cursor = conn.cursor()
    cursor.execute(sql, ( order_num, system_content, user_content, assistant_content,row_id))
    cursor.close()
    conn.commit()
    conn.close()
    return jsonify({"code":1,"msg":"modify ok!"})

@setapi.route('/delete',methods=['delete'])
def data_set_delete():
    conn = sqlite3.connect(db_file_path)
    row_id = request.args.get('id')
    sql = """    delete from  base_dataset where id = ?;  """
    cursor = conn.cursor()
    cursor.execute(sql, (  row_id,))
    cursor.close()
    conn.commit()
    conn.close()
    return jsonify({"code":1,"msg":"delete ok!"})


@setapi.route('/export',methods=['POST'])
def data_set_export():
    conn = sqlite3.connect(db_file_path)
    conn.row_factory = sqlite3.Row
    sql = """  select * from  base_dataset order by  orderNum"""
    cursor = conn.cursor()
    cursor.execute(sql )
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    file  = open('train.jsonl','w+',  encoding='utf-8')
    for row in rows:
        train = {
            "messages":[
                {  "role":'system',  "content":row['systemContent'] },
                {  "role":'user',  "content":row['userContent'] },
                {  "role":'assistant',  "content":row['assistantContent']}
            ]
        }
        content = json.dumps(train,ensure_ascii=False)
        file.write(content + "\n")
    file.close()
    return jsonify({"code":1,"data":'export ok'})